﻿CREATE PROCEDURE CAT.AdjustProductsPrice
	@index FLOAT = NULL,

	@checkValidation BIT = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,
		--To be used to store the value of the global variable @@ROWCOUNT
		@rowsCount INT


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '@index: ' + ISNULL(CAST(@index AS VARCHAR), '')
		+ ', @checkValidation: ' + ISNULL(CAST(@checkValidation AS VARCHAR), '')


	-----------------------
	-- Transaction variables
	-----------------------
	DECLARE
		--This variable is used as a checkpoint for handle nested transactions.
		@rollbackPoint NCHAR(32),
		--Indicates if the transaction to be commited is owned by the current procedure.
		@ownTransaction BIT

	SET @ownTransaction = 0
		

	-----------------------
	-- Parameters defaults
	-----------------------
	IF (@checkValidation IS NULL)
		SET @checkValidation = 1


	-----------------------
	-- Parameters validation
	-----------------------
	IF (@checkValidation = 1)
	BEGIN
		IF ((@result = 0) AND (@index IS NULL))
		BEGIN
			RAISERROR('Los argumentos %s no pueden ser nulos o vacíos.', 11, 1, '"Índice"')
			SET @result = @@ERROR --Set @result with the current @@Error value.	
		END

		IF ((@result = 0) AND (@index <= -1))
		BEGIN
			RAISERROR('El argumento %s tiene que ser mayor a "-1".', 11, 1, '"Índice"')
			SET @result = @@ERROR --Set @result with the current @@Error value.	
		END
	END


	-----------------------
	-- Begin Transaction
	-----------------------
	IF (@result = 0)
	BEGIN
		PRINT @procName + 'Attempt to adjust the price of all products.'

		IF (@@TRANCOUNT > 0)
		BEGIN
			SET @rollbackPoint = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'')
			SAVE TRANSACTION @rollbackPoint
		END
		ELSE
		BEGIN
			SET @ownTransaction = 1
			BEGIN TRANSACTION
		END

		SET @result = @@ERROR --Set @result with the current @@Error value.


		-----------------------
		-- Insert
		-----------------------
		IF (@result = 0)
		BEGIN
			UPDATE CAT.Products
			SET
				Price = Price * @index
			--WHERE
			--	Active = 1


			SELECT
				@rowsCount = @@ROWCOUNT,
				@result = @@ERROR --Set @result with the current @@Error value.

			IF ((@result = 0) AND (@rowsCount = 0))
			BEGIN
				RAISERROR('Los precios no fueron ajustados.', 11, 1)
				SET @result = @@ERROR --Set @result with the current @@Error value.	
			END
		END


		-----------------------
		-- End Transaction
		-----------------------

		--If @result = 0, attempt to commit the transaction. Else rollback it.
		IF (@result = 0)
		BEGIN
			IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Commit transaction'
				COMMIT TRANSACTION
			END
		END
		ELSE
		BEGIN
			IF ((@rollbackPoint IS NOT NULL) AND (XACT_STATE() = 1))
			BEGIN
				PRINT @procName + 'Rollback transaction point ' + @rollbackPoint
				ROLLBACK TRANSACTION @rollbackPoint;
			END
			ELSE IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Rollback transaction'
				ROLLBACK TRANSACTION;
			END
		END
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @procName + @finishEndMSG
	ELSE 
		PRINT @procName + @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR)

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::CAT.AdjustProductsPrice TO [CatalogEditor]
    AS [dbo];